#pragma once

#include <cppconn/driver.h>
#include <cppconn/connection.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <cppconn/metadata.h>
#include <cppconn/resultset_metadata.h>
#include <cppconn/exception.h>
#include <mysql-cppconn/jdbc/cppconn/warning.h>

#include <jsoncpp/json/json.h>
#include <string>
#include <vector>
#include <chrono>

using namespace sql;

const static std::string DbHost = "tcp://127.0.0.1:3306";
const static std::string User = "chen";
const static std::string Passwd = "cyd1720036941";
const static std::string Database = "ManagementSystem";

class Staff
{
public:
    void Serialize(Json::Value &root)
    {
        root["id"] = id.c_str();
        root["authority"] = authority.c_str();
        root["name"] = name.c_str();
        root["sex"] = sex.c_str();
        root["birthday"] = birthday.c_str();
        root["department"] = department.c_str();
        root["position"] = position.c_str();
        root["education"] = education.c_str();
        root["skill"] = skill.c_str();
        root["address"] = address.c_str();
        root["tel"] = tel.c_str();
        root["email"] = email.c_str();
        root["status"] = status.c_str();
        root["remark"] = remark.c_str();
    }

    void DeSerialize(const Json::Value &T)
    {
        id = T["id"].asString();
        authority = T["authority"].asString();
        name = T["name"].asString();
        sex = T["sex"].asString();
        birthday = T["birthday"].asString();
        department = T["department"].asString();
        position = T["position"].asString();
        education = T["education"].asString();
        skill = T["skill"].asString();
        address = T["address"].asString();
        tel = T["tel"].asString();
        email = T["email"].asString();
        status = T["status"].asString();
        remark = T["remark"].asString();
    }

    std::string id;
    std::string authority;
    std::string name;
    std::string sex;
    std::string birthday;
    std::string department;
    std::string position;
    std::string education;
    std::string skill;
    std::string address;
    std::string tel;
    std::string email;
    std::string status;
    std::string remark;
};

class Department
{
public:
    void Serialize(Json::Value &root)
    {
        root["id"] = id.c_str();
        root["name"] = name.c_str();
        root["manager"] = manager.c_str();
        root["intro"] = intro.c_str();
    }

    std::string id;
    std::string name;
    std::string manager;
    std::string intro;
};

class PersonNel
{
public:
    void Serialize(Json::Value &root)
    {
        root["id"] = id.c_str();
        root["name"] = personid.c_str();
        root["manager"] = code.c_str();
        root["intro"] = desc.c_str();
    }

    std::string id;
    std::string personid;
    std::string code;
    std::string desc;
};

class Mysql
{
private:
    void Hander(std::vector<Staff> &list, ResultSet *res)
    {
        while (res->next())
        {
            struct Staff staff;
            staff.id = res->getString("ID");
            staff.authority = res->getString("AUTHORITY");
            staff.name = res->getString("NAME");
            staff.sex = res->getString("SEX");
            staff.birthday = res->getString("BIRTHDAY");
            staff.department = res->getString("DEPARTMENT");
            staff.position = res->getString("JOB");
            staff.education = res->getString("EDU_LEVEL");
            staff.skill = res->getString("SPECIALTY");
            staff.address = res->getString("ADDRESS");
            staff.tel = res->getString("TEL");
            staff.email = res->getString("EMAIL");
            staff.status = res->getString("STATE");
            staff.remark = res->getString("REMARK");

            list.push_back(staff);
        }
    }

public:
    Mysql()
    {
        driver = get_driver_instance();
        conn = driver->connect(DbHost.c_str(), User.c_str(), Passwd.c_str());
        stmt = conn->createStatement();

        stmt->execute(("use " + Database).c_str());

        deps = SearchDepartment();
    }
    ~Mysql()
    {
        delete stmt;
        delete conn;
    }

    bool SearchOne(const std::string &ID, const std::string &Passwd)
    {
        ResultSet *res;
        char buffer[128];
        snprintf(buffer, sizeof(buffer), "select ID,PASSWD from users where ID='%s' and PASSWD='%s'", ID.c_str(), Passwd.c_str());

        std::string query = buffer;
        res = stmt->executeQuery(query.c_str());
        while (res->next())
        {
            if (res->getString("ID") != "")
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        return false;
    }

    bool SearchId(const std::string &ID)
    {
        ResultSet *res;

        char buffer[256];
        snprintf(buffer, sizeof(buffer), "select ID from users where ID='%s'", ID.c_str());

        std::string query = buffer;

        res = stmt->executeQuery(query.c_str());
        while (res->next())
        {
            if (res->getString("ID") != "")
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        return false;
    }

    bool InsertIntoUsers(const std::string &ID, const std::string &Passwd)
    {
        char buffer[256];
        snprintf(buffer, sizeof(buffer), "insert into users (ID, PASSWD) values('%s', '%s')", ID.c_str(), Passwd.c_str());

        std::string query = buffer;
        try
        {
            stmt->execute(query.c_str());
        }
        catch (sql::SQLException &e)
        {
            return false;
        }
        return true;
    }

    // 无参查找，查找全部
    std::vector<Staff> Search()
    {
        std::vector<Staff> list;
        ResultSet *res = stmt->executeQuery("select * from PERSON");

        Hander(list, res);

        return list;
    }

    std::vector<Department> SearchDepartment()
    {
        std::vector<Department> list;
        ResultSet *res = stmt->executeQuery("select * from DEPARTMENT");

        while (res->next())
        {
            Department dep;
            dep.id = res->getString("ID");
            dep.name = res->getString("NAME");
            dep.manager = res->getString("MANAGER");
            dep.intro = res->getString("INTRO");

            list.push_back(dep);
        }

        return list;
    }

    std::vector<PersonNel> SearchPersonNel()
    {
        std::vector<PersonNel> list;
        ResultSet *res = stmt->executeQuery("select * from PERSONNEL");

        while (res->next())
        {
            PersonNel dep;
            dep.id = res->getString("ID");
            dep.personid = res->getString("PERSON");
            dep.code = res->getString("CHANGE");
            dep.desc = res->getString("DESCRIPTION");

            list.push_back(dep);
        }

        return list;
    }

    bool InsertIntoPerson(const Staff &person)
    {
        char buffer[300];
        snprintf(buffer, sizeof(buffer), "insert into PERSON values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');",
                 person.id.c_str(),
                 person.authority.c_str(),
                 person.name.c_str(),
                 person.sex.c_str(),
                 person.birthday.c_str(),
                 person.department.c_str(),
                 person.position.c_str(),
                 person.education.c_str(),
                 person.skill.c_str(),
                 person.address.c_str(),
                 person.tel.c_str(),
                 person.email.c_str(),
                 person.status.c_str(),
                 person.remark.c_str());

        std::cout << buffer << std::endl;

        std::string query = buffer;
        try
        {
            stmt->execute(query.c_str());
        }
        catch (sql::SQLException &e)
        {
            return false;
        }
        return true;
    }

    // 带参的查找
    std::vector<Staff> Search(const std::string &term, const std::string &content)
    {
        char buffer[256];
        snprintf(buffer, sizeof(buffer), "select * from PERSON where '%s'='%s'", term.c_str(), content.c_str());

        std::string query = buffer;
        ResultSet *res;
        res = stmt->executeQuery(query.c_str());

        std::vector<Staff> list;
        Hander(list, res);

        return list;
    }

    bool DeleteOne(const std::string &id)
    {
        char buffer[256];
        snprintf(buffer, sizeof(buffer), "delete from PERSON where ID='%s'", id.c_str());
        std::string query = buffer;

        return !stmt->execute(query.c_str());
    }

private:
    sql::Driver *driver;
    sql::Connection *conn;
    sql::Statement *stmt;

    std::vector<Department> deps;
};